﻿<%@ Page Title="Home Page" Language="vb" EnableEventValidation="false" MasterPageFile="~/Bootstrap.Master" AutoEventWireup="false"
    CodeBehind="viewStudents3.aspx.vb" Inherits="SATS._Default3" %>
<asp:Content ID="WarningContent" ContentPlaceHolderID="WarningContent" runat="server">
    <asp:ValidationSummary ID="ValidationSummary" runat="server" />
</asp:Content>
<asp:Content ID="MainContent" runat="server" ContentPlaceHolderID="MainContent">
    <div class="row-fluid">
        <div class="span12">
            <h2 class="style5">View Students</h2>
            <hr />
        </div>
    </div>
    <div class="row-fluid">
        <center>
        <div class="span3">
            <asp:Label ID="Label14" runat="server" Text="Department:" Font-Names="Arial"
                Font-Size="Small"></asp:Label>
            <br />
            <asp:DropDownList ID="ddDepartment" runat="server" Height="24px"
                Width="156px" DataSourceID="AllDeptName" DataTextField="DepartmentName" DataValueField="DepartmentName" AppendDataBoundItems="true">
                <asp:ListItem Text="All" Value="%"></asp:ListItem>
            </asp:DropDownList>
            <asp:SqlDataSource ID="AllDeptName" runat="server" ConnectionString="<%$ ConnectionStrings:CIS4396S03LocalSSHTunnelString %>" SelectCommand="SELECT DISTINCT [DepartmentName] FROM [Departments]" ProviderName="<%$ ConnectionStrings:CIS4396S03LocalSSHTunnelString.ProviderName %>"></asp:SqlDataSource>
        </div>
        <div class="span3">
            <asp:Label ID="Label9" runat="server" Text="Start Term:"
                Font-Names="Arial" Font-Size="Small"></asp:Label>
            <br />
            <asp:DropDownList ID="ddStartTerm" runat="server" Height="24px"
                Width="156px" DataSourceID="AllStartTerm" DataTextField="TermName" DataValueField="TermName" AppendDataBoundItems="true">
                <asp:ListItem Text="All" Value="%"></asp:ListItem>
            </asp:DropDownList>
            <asp:SqlDataSource ID="AllStartTerm" runat="server" ConnectionString="<%$ ConnectionStrings:CIS4396S03LocalSSHTunnelString %>" SelectCommand="SELECT TermName FROM Term WHERE TermID BETWEEN (SELECT StartID FROM TermRef WHERE TermRefID=1) AND (SELECT EndID FROM TermRef WHERE TermRefID=1) ORDER BY TermID"></asp:SqlDataSource>
        </div>
        <div class="span3">
            <asp:Label ID="Label7" runat="server" Text="Last Name:"
                Font-Names="Arial" Font-Size="Small"></asp:Label>
            <br />
            <asp:TextBox ID="txtFirstName" runat="server" Width="90px" Height="18px"></asp:TextBox>
        </div>
        <div class="span3">
            <asp:Label ID="Label17" runat="server" Text="User ID:" Font-Names="Arial"
                Font-Size="Small"></asp:Label>
            <br />
            <asp:TextBox ID="txtUserID" runat="server" Width="90px" Height="18px"></asp:TextBox>
        </div>
        </center>
    </div>

    <div class="row-fluid">
        <center>
        <div class="span3">
            <asp:Label ID="Label15" runat="server" Text="Organization:" Font-Names="Arial"
                Font-Size="Small"></asp:Label>
            <br />
            <asp:DropDownList ID="ddOrganization" runat="server" Height="24px"
                Width="156px" Style="margin-left: 3px" DataSourceID="AllOrganization" DataTextField="organizationName" DataValueField="organizationName" AppendDataBoundItems="true">
                <asp:ListItem Text="All" Value="%"></asp:ListItem>
            </asp:DropDownList>
            <asp:SqlDataSource ID="AllOrganization" runat="server" ConnectionString="<%$ ConnectionStrings:CIS4396S03LocalSSHTunnelString %>" SelectCommand="SELECT DISTINCT [organizationName] FROM [Organizations]"></asp:SqlDataSource>
        </div>
        <div class="span3">
            <asp:Label ID="Label10" runat="server" Text="End Term:"
                Font-Names="Arial" Font-Size="Small"></asp:Label>
            <br />
            <asp:DropDownList ID="ddEndTerm" runat="server" Height="24px"
                Width="156px" DataSourceID="AllEndTerm" DataTextField="TermName" DataValueField="TermName" AppendDataBoundItems="true">
                <asp:ListItem Text="All" Value="%"></asp:ListItem>
            </asp:DropDownList>
            <asp:SqlDataSource ID="AllEndTerm" runat="server" ConnectionString="<%$ ConnectionStrings:CIS4396S03LocalSSHTunnelString %>" SelectCommand="SELECT TermName FROM Term WHERE TermID BETWEEN (SELECT StartID FROM TermRef WHERE TermRefID=1) AND (SELECT EndID FROM TermRef WHERE TermRefID=1) ORDER BY TermID"></asp:SqlDataSource>
        </div>
        <div class="span3">
            <asp:Label ID="Label8" runat="server" Text="First Name:" Font-Names="Arial"
                Font-Size="Small"></asp:Label>
            <br />
            <asp:TextBox ID="txtLastName" runat="server" Width="90px" Height="18px"></asp:TextBox>
        </div>
        <div class="span3">
            <asp:Label
                ID="Label16" runat="server" Text="TU ID:" Font-Names="Arial" Font-Size="Small"></asp:Label>
            <br />
            <asp:TextBox ID="txtTUID" runat="server" Width="90px" Height="18px"></asp:TextBox>
        </div>
        </center>
    </div>

    <div class="row-fluid">
        <div class="span12">
            <asp:Button ID="Button1" runat="server" Text="Filter"
                Width="100px" Height="30px" BackColor="#990000" ForeColor="#FFFFCC" />
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            <asp:Button ID="ClearButton" runat="server" Text="Clear Filter"
                Width="100px" Height="30px" BackColor="#990000" ForeColor="#FFFFCC" />
            <br />
            <br />
        </div>
    </div>

    <div class="row-fluid">
        <div class="span12">
            <center>
            <asp:GridView ID="gvStudentList" runat="server" AllowPaging="True" AllowSorting="True" OnSorting="SortGridView" AutoGenerateColumns="False"
                CellPadding="4" ForeColor="#333333" GridLines="None" Width="865px" DataSourceID="ViewStudentGV"
                DataKeyNames="involvementID" OnRowDeleting="gvAdminList_RowDeleting" PageSize="15">
                <AlternatingRowStyle BackColor="White" />
                <Columns>
                    <asp:BoundField DataField="tuID" HeaderText="TU ID" SortExpression="tuID" />
                    <asp:BoundField DataField="firstName" HeaderText="Last Name" SortExpression="firstName" />
                    <asp:BoundField DataField="lastName" HeaderText="First Name" SortExpression="lastName" />
                    <asp:BoundField DataField="DepartmentName" HeaderText="Department" SortExpression="DepartmentName" />
                    <asp:BoundField DataField="organizationName" HeaderText="Organization" SortExpression="organizationName" />
                    <asp:BoundField DataField="classification" HeaderText="Classification" SortExpression="classification" />
                    <asp:BoundField DataField="role" HeaderText="Role" SortExpression="role" />
                    <asp:BoundField DataField="startingTerm" HeaderText="Start Term" />
                    <asp:BoundField DataField="endingTerm" HeaderText="End Term" />
                    <asp:BoundField DataField="comments" HeaderText="Comments" SortExpression="comments" />
                    <asp:CommandField DeleteText="Remove" ShowDeleteButton="True" />
                </Columns>

                <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
                <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
                <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
                <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
                <SortedAscendingCellStyle BackColor="#FDF5AC" />
                <SortedAscendingHeaderStyle BackColor="#4D0000" />
                <SortedDescendingCellStyle BackColor="#FCF6C0" />
                <SortedDescendingHeaderStyle BackColor="#820000" />
            </asp:GridView>
                </center>
            <br />
        </div>
    </div>

    <div class="row-fluid">
        <div class="span12">
            <asp:Button ID="btnExport" runat="server" Height="31px"
                Style="text-align: center" Text="Download as Excel file" Width="193px" BackColor="#990000" ForeColor="#FFFFCC" />
        </div>
    </div>


    <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" Display="Dynamic"
        ControlToValidate="txtTUID"
        ErrorMessage="Incorrect TUID format"
        ValidationExpression="\d{9}" />

    <asp:SqlDataSource ID="ViewStudentGV" runat="server" ConnectionString="<%$ ConnectionStrings:CIS4396S03LocalSSHTunnelString %>" SelectCommand="SELECT i.tuID, u.firstName, u.lastName, u.accessNetID, d.DepartmentName, 
             o.organizationName, i.classification, i.role, 
            (SELECT t.TermName WHERE i.startTerm = t.TermID) as startingTerm, 
            (SELECT t2.TermName WHERE i.endTerm = t2.TermID) as endingTerm,
            t.TermID as startTermID, t2.TermID as endTermID,
            i.Comments, i.involvementID FROM Involvments i
            INNER JOIN Users u ON i.tuID = u.tuID
            INNER JOIN Departments d ON i.departmentID = d.DepartmentID
            INNER JOIN Organizations o ON i.organizationID = o.organizationID
            INNER JOIN Term t on i.startTerm = t.TermID
            INNER JOIN Term t2 on i.endTerm = t2.TermID"
        DeleteCommand="DELETE FROM Involvments WHERE involvementID = @InvolvementID"
        FilterExpression="DepartmentName like '{0}%' and startTermID >= '{1}' and organizationName like '{2}%' and endTermID <= '{3}'
                and firstName like '%{4}%' and accessNetID like '%{5}%' and lastName like '%{6}%'
                and Convert(tuID, 'System.String') like '%{7}%'">
        <FilterParameters>
            <asp:ControlParameter Name="DepartmentName" ControlID="ddDepartment"
                PropertyName="SelectedValue" Type="String" />
            <asp:ControlParameter Name="startTermID" Type="Int32" />
            <asp:ControlParameter Name="organizationName" ControlID="ddOrganization"
                PropertyName="SelectedValue" Type="String" />
            <asp:ControlParameter Name="endTermID" Type="Int32" />
            <asp:ControlParameter Name="firstName" ControlID="txtFirstName"
                PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />
            <asp:ControlParameter Name="accessNetID" ControlID="txtUserID"
                PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />
            <asp:ControlParameter Name="lastName" ControlID="txtLastName"
                PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />
            <asp:ControlParameter Name="tuID" ControlID="txtTUID"
                PropertyName="Text" Type="String" ConvertEmptyStringToNull="false" />
        </FilterParameters>
        <DeleteParameters>
            <asp:Parameter Name="involvementID" Type="Int32" />
        </DeleteParameters>
    </asp:SqlDataSource>
</asp:Content>

<asp:Content ContentPlaceHolderID="Instructions" ID="instructions" runat="server">
    <ul>
        <li>To sort the data by column, click the column's heading</li>
        <br />
        <li>The data can be downloaded to an Excel file through the button at the bottom of the screen</li>
        <br />
        <li>If searching by term, please be sure to select both a Start Term AND End Term.</li>
    </ul>
</asp:Content>